﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Windows.Forms;
using MeTalker.Aide.Model;
using MeTalker.Aide.Forms.Mode;

namespace MeTalker.Aide.DBUtility
{
    internal class AccessHelper
    {
        internal static string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data\\data.mdb";
        static OleDbConnection conn;// = new OleDbConnection(ConnectionString);

        internal static void CloseConnect()
        {
            if (conn != null && conn.State == ConnectionState.Open)
                conn.Close();
        }
        static AccessHelper()
        {
            conn = new OleDbConnection(ConnectionString);
        }

        static int Execute(string sql)
        {
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                if (conn != null && conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd.CommandText = sql;
                cmd.Connection = conn;
                return cmd.ExecuteNonQuery();
            }
            finally {
                if (conn != null)
                    conn.Close();
            }
        }

        #region 客户留言
        internal static int InsertCustomMsg(CustomMsg msg)
        {
            try
            {
                string sql = string.Format("insert into tab_CustomMsg(WriterTime,CustomName,Content,ReceName) values('{0}','{1}','{2}','{3}')", msg.Time, msg.CustomName, msg.Content, msg.ReceName);
                return Execute(sql);
            }
            catch
            {
                return 0;
            }
        }

        internal static int DeleteCustomMsg(int id)
        {
            try
            {
                string sql = string.Format("delete from tab_CustomMsg where Id={0}", id);
                return Execute(sql);
            }
            catch
            {
                return 0;
            }
        }

        internal static int DeleteAllCustomMsg()
        {
            try
            {
                string sql = "delete from tab_CustomMsg";
                return Execute(sql);
            }
            catch
            {
                return 0;
            }
        }
        internal static DataSet GetCustomMsgs()
        {
            try
            {
                string sql = "select * from tab_CustomMsg";
                using (OleDbConnection mycon = new OleDbConnection(ConnectionString))
                {
                    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, mycon);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 问题与答案
        internal static List<AnswerMod> GetProblemList()
        {
            //return GetProblemsReader();
            //DataSet ds = GetProblems();
            List<AnswerMod> answers = new List<AnswerMod>();
            List<AnswerMod> temp = GetProblemsReader();
            foreach (var data in temp)
            {
                string msg = "问题:" + (data.Problem.Replace("\r\n", "  问题:"));
                answers.Add(new AnswerMod() { Id = data.Id, Answer = data.Answer, Problem = msg });
            }
            return answers;
        }

        internal static List<AnswerMod> GetProblemsReader()
        {
            try
            {
                List<AnswerMod> answers = new List<AnswerMod>();
                string sql = "SELECT * FROM tab_Problem";

                OleDbCommand cmd = new OleDbCommand(sql, conn);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    answers.Add(new AnswerMod() { Id = reader.GetInt32(0), Problem = reader.GetString(1), Answer = reader.GetString(2) });
                }
                return answers;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            //using (OleDbConnection mycon = new OleDbConnection(ConnectionString))
            //{
            //    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, mycon);
            //DataSet ds = new DataSet();
            //    adapter.Fill(ds);
            //return ds;
            //}
        }

        internal static AnswerMod GetAnswer(DataRow row)
        {
            return new AnswerMod() { Id = int.Parse(row["Id"].ToString()), Answer = row["Answer"].ToString(), Problem = row["Problem"].ToString() };
        }

        internal static AnswerMod GetProblem(int id)
        {
            string sql = "SELECT * FROM tab_Problem where Id=" + id;
            using (OleDbConnection mycon = new OleDbConnection(ConnectionString))
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, mycon);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
                    return GetAnswer(ds.Tables[0].Rows[0]);
                return null;
            }
        }

        internal static int InsertProblem(AnswerMod answer)
        {
            try
            {
                string sql = string.Format("insert into tab_Problem(problem,answer) values('{0}','{1}')", answer.Problem, answer.Answer);
                return Execute(sql);
            }
            catch { return 0; }
        }
        internal static int UpdateProblem(AnswerMod answer)
        {
            try
            {
                string sql = string.Format("update tab_Problem set problem='{0}', answer='{1}' where id={2}", answer.Problem, answer.Answer, answer.Id);
                return Execute(sql);
            }
            catch { return 0; }
        }
        internal static int DeleteProblem(int id)
        {
            try
            {
                string sql = string.Format("delete from tab_Problem where id={0}", id);
                return Execute(sql);
            }
            catch { return 0; }
        }
        #endregion
    }
}